﻿Imports System.Windows.Forms

Public Class frmSearchTables

    Private IsCancelled As Boolean = False
    Private dgvResults As WRDB.Controls.DGVEditor

    Private Sub frmSearchTables_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        IsCancelled = True
        SaveWindowPos(REGAPP, Me)
    End Sub

    Private Sub frmSearchTables_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        GetWindowPos(REGAPP, Me)
        Project.DB.FillPickList(cboStationID, "", "Stations", "Station_ID", "Station_Name", "(Any)")
        Project.DB.FillPickList(cboPCode, "", "PCodes", "PCode", "Anal_Name", "(Any)")
        dgResults.Visible = False
        lnkOpen.Visible = False
        dgvResults = New WRDB.Controls.DGVEditor(dgResults)
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Try
            Application.UseWaitCursor = True
            lnkOpen.Visible = False
            dgResults.Visible = False
            btnSearch.Enabled = False
            btnClose.Text = "Cancel"
            Dim dt As New DataTable
            With dt
                .Columns.Add("Table Name", GetType(String))
                .Columns.Add("Station ID", GetType(String))
                .Columns.Add("PCode", GetType(String))
                .Columns.Add("No. Records", GetType(Integer))
                .Columns.Add("Min Date", GetType(Date))
                .Columns.Add("Max Date", GetType(Date))
            End With
            With Project
                Dim tblList As Generic.List(Of String) = .DB.GetTableList
                tblList.Sort()
                For i As Integer = 0 To tblList.Count - 1
                    Dim tblName As String = tblList(i)
                    Dim TblType As clsProject.enumTableTypes = .GetTableType(tblName)
                    If TblType = clsProject.enumTableTypes.Working Or TblType = clsProject.enumTableTypes.Master Then
                        If Not MainForm.WriteProgress(i, tblList.Count - 1, "Searching table " & tblName, ProgressBarStyle.Blocks, True) Then Exit Sub
                        Dim sqlString As String = String.Format("SELECT MIN(Station_ID),MIN(PCode),Count(Result),MIN(Date_Time),MAX(Date_Time) FROM {0}", .DB.FullTableName(tblName))
                        Dim whereClause As String = ""
                        If cboStationID.Text <> "(Any)" Then
                            whereClause &= String.Format("Station_ID LIKE '{0}'", .DB.GetPickList(cboStationID).Replace("*", "%"))
                        End If
                        If cboPCode.Text <> "(Any)" Then
                            If whereClause <> "" Then whereClause &= " AND "
                            whereClause &= String.Format("PCode LIKE '{0}'", .DB.GetPickList(cboPCode).Replace("*", "%"))
                        End If
                        If whereClause <> "" Then sqlString &= " WHERE " & whereClause
                        sqlString &= " GROUP BY Station_ID,PCode"
                        Try
                            Application.DoEvents()
                            If IsCancelled Then Exit Sub
                            Dim dr As DbDataReader = .DB.ExecuteReader(sqlString)
                            While dr IsNot Nothing And dr.Read
                                Dim NumRec As Integer = dr.GetValue(2)
                                If NumRec > 0 Then
                                    dt.Rows.Add(tblName, dr.GetValue(0), dr.GetValue(1), NumRec, TestNull(dr.GetValue(3), Date.MinValue), TestNull(dr.GetValue(4), Date.MaxValue))
                                End If
                            End While
                            dr.Close()
                        Catch ex As Exception
                            dt.Rows.Add(tblName & " (Invalid)")
                        End Try
                    End If
                Next
            End With
            With dgResults
                .DataSource = dt
                .Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                For i As Integer = 4 To 5
                    With .Columns(i).DefaultCellStyle
                        .Alignment = DataGridViewContentAlignment.MiddleRight
                        .Format = MyDateFormat()
                    End With
                Next
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                If .RowCount < 100 Then
                    .AutoResizeColumns()
                Else
                    .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
                End If
                .ReadOnly = True
                .AllowUserToOrderColumns = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                For i As Integer = 0 To .Columns.Count - 1
                    With .Columns(i)
                        .SortMode = DataGridViewColumnSortMode.NotSortable
                    End With
                Next
                .Visible = True
                If .RowCount > 0 Then lnkOpen.Visible = True
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            btnClose.Text = "Close"
            btnSearch.Enabled = True
            Application.UseWaitCursor = False
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        IsCancelled = True
        If btnClose.Text = "Close" Then Me.Close()
    End Sub

    Private Sub lnkOpen_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkOpen.LinkClicked
        For i As Integer = 0 To dgResults.RowCount - 1
            Dim tblName As String = dgResults.Rows(i).Cells(0).Value
            If Not WRDBForms.Contains(tblName) Then MainForm.OpenDataTable(tblName)
        Next
    End Sub

End Class